package main.dao;

import java.util.List;

import main.entity.ReplyVO;
import main.entity.ThreadsVO;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@SuppressWarnings("unchecked")
@Repository("threadsDAO")
public class ThreadsDAO extends BaseDAO<ThreadsVO> {

	@Autowired
	private SessionFactory factory;

	public List<ThreadsVO> getByChannel(Integer channelID) {
		// String queryString =
		// "FROM Threads t, Channel c WHERE t.chnl_ID=c.channel_ID AND c.channel_ID=?";
		String queryString = "FROM main.entity.ThreadsVO t WHERE t.channelID=?";
		List<ThreadsVO> threadsVOs = (List<ThreadsVO>) getHibernateTemplate()
				.find(queryString, channelID);
		return threadsVOs;
	}

	public List<?> getByUID(Integer uID) {
		String queryString = "FROM main.entity.ThreadsVO t WHERE t.uID=?";
		List<?> threadsVOs = getHibernateTemplate().find(queryString, uID);
		return threadsVOs;
	}

	public List<?> readHotThreads() {
		// String queryString =
		// "FROM main.entity.ThreadsVO as t order by t.threadsID";
		// List<?> list = getHibernateTemplate().find(queryString);
		// return list;

		// Configuration config = new Configuration().configure();
		// SessionFactory factory = config.buildSessionFactory();
		Session session = factory.openSession();
		String hql = "FROM main.entity.ThreadsVO";
		int curpage = 1;// 当前页
		int pagesize = 5;// 每页显示数
		List<ThreadsVO> list = (List<ThreadsVO>) readOnePage(session, hql, curpage, pagesize);
		for (ThreadsVO threadsVO : list) {
			Integer threadsID = threadsVO.getThreadsID();
			String strHQL = "SELECT COUNT(r) FROM main.entity.ReplyVO r WHERE r.threads_ID=:threadsID";
			Query query = session.createQuery(strHQL);
			query.setInteger("threadsID", threadsID);
			List<Long> resList = query.list();
			Long count = resList.get(0);
			threadsVO.setReplyCount(count.intValue());
		}
//		System.out.println(list);
		
		String sqlsString = "SELECT t.*, sub.replyCount FROM threads AS t, (SELECT count(r.rpl_ID) AS replyCount,threads_ID from reply AS r GROUP BY threads_ID ORDER BY replyCount DESC LIMIT 0,5) AS sub WHERE t.threads_ID = sub.threads_ID";
		SQLQuery sqlQuery = session.createSQLQuery(sqlsString).addEntity(ThreadsVO.class);
		List<ThreadsVO>threadsVOs = (List<ThreadsVO>) sqlQuery.list();
		return threadsVOs;
	}

	public static List<?> readOnePage(Session session, String strHQL,
			Integer offset, Integer pagesize) {
		List<?> lst = null;
		try {
			Query query = session.createQuery(strHQL);
			if (offset != 0 && pagesize != 0) {
				query.setFirstResult((offset - 1) * pagesize);
				query.setMaxResults(pagesize);
			}
			lst = query.list();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return lst;
	}

}
